//
//  HXDataBaseObject.m
//  HXTClient
//
//  Created by Apple on 14-5-17.
//  Copyright (c) 2014年 easaa. All rights reserved.
//

#import "HXDataBaseObject.h"

NSString *const DataBaseValueKey = @"DataBaseValueKey";
NSString *const DataBaseValue = @"DataBaseValue";

@implementation HXDataBaseObject

+ (instancetype)shareDataBaseObject
{
    static HXDataBaseObject *dataBaseObject;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBaseObject = [[HXDataBaseObject alloc] init];
    });
    return dataBaseObject;
}

- (id)init
{
    self = [super init];
    if (self)
    {
        int res = pthread_mutex_init(&mutex, NULL);
        if (res != 0)
            HXLog(@"创建失败!");
    }
    return self;
}

- (NSString *)getDataBasePath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *strPath = [paths objectAtIndex:0];
    strPath = [strPath stringByAppendingPathComponent:HXDataBaseFileName];
    return strPath;
}

- (BOOL)openDataBase
{
    pthread_mutex_lock(&mutex);
    if (sqlite3_open([[self getDataBasePath] UTF8String], &db) == SQLITE_OK)
        return YES;
    return NO;
}

- (BOOL)closeDataBase
{
    if (sqlite3_close(db) == SQLITE_OK)
    {
         db = NULL;
        pthread_mutex_unlock(&mutex);
        return YES;
    }
    pthread_mutex_unlock(&mutex);
    return NO;
}

- (BOOL)createNewTableWithName:(NSString *)tableName
{
    NSString *strCreateTableSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (ValueKey TEXT PRIMARY KEY, Value TEXT);", tableName];
    char *chCreatTableErrMsg = NULL;
    if (sqlite3_exec(db, [strCreateTableSQL UTF8String], NULL, NULL, &chCreatTableErrMsg) != SQLITE_OK)
    {//建表失败
        sqlite3_free(chCreatTableErrMsg);
        return NO;
    }
    return YES;
}

- (int)queryDataCountInTable:(NSString *)tableName
{
    NSString *strSQl = [NSString stringWithFormat:@"SELECT * FROM %@;", tableName];
    int count = 0;
    sqlite3_stmt *stmt = NULL;
    if (sqlite3_prepare_v2(db, [strSQl UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            count = sqlite3_column_count(stmt);   //获取结果集中的数据条数
        }
    }
    sqlite3_finalize(stmt); //释放资源
    return count;
}

- (BOOL)insertData:(NSArray *)aryData toTable:(NSString *)tableName
{
    BOOL insertResult = YES;
    @try {
        
        for (NSDictionary *dicData in aryData)
        {
            char *errorMsg = NULL;
            if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errorMsg) == SQLITE_OK)
            {//事务启动成功
                HXLog(@"事务启动成功");
                sqlite3_stmt *stmt = NULL;
                NSString *strSQL = [NSString stringWithFormat:@"INSERT INTO %@ (ValueKey, Value) values (?, ?);", tableName];
                if (sqlite3_prepare_v2(db, [strSQL UTF8String], -1, &stmt, NULL) == SQLITE_OK)
                {
                    const char *valueKey = [[dicData objectForKey:DataBaseValueKey] UTF8String];
                    const char *value = [[dicData objectForKey:DataBaseValue] UTF8String];
                    sqlite3_bind_text(stmt, 1, valueKey, strlen(valueKey), SQLITE_STATIC);
                    sqlite3_bind_text(stmt, 2, value, strlen(value), SQLITE_STATIC);
                    
                    if (sqlite3_step(stmt) == SQLITE_DONE)
                    {
                        if (sqlite3_exec(db,"COMMIT", NULL, NULL, &errorMsg) != SQLITE_OK)
                        {//如果事务提交失败则回滚
                            char *rollbackErrMsg = NULL;
                            sqlite3_exec(db, "ROLLBACK", NULL, NULL, &rollbackErrMsg);
                            sqlite3_free(rollbackErrMsg);
                            
                            insertResult = NO;
                        }
                    } else
                    {//如果执行失败，则回滚
                        char *rollbackErrMsg = NULL;
                        sqlite3_exec(db, "ROLLBACK", NULL, NULL, &rollbackErrMsg);
                        sqlite3_free(rollbackErrMsg);
                        
                        insertResult = NO;
                    }
                }
                sqlite3_finalize(stmt);
            } else
            {
                sqlite3_free(errorMsg);
                insertResult = NO;
            }
        }
    }
    @catch (NSException *exception) {
        char *rollbackErrMsg = NULL;
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, &rollbackErrMsg);
        sqlite3_free(rollbackErrMsg);
    }
    @finally {
        
    }
    return insertResult;
}

//-(int) update
//{
//    int result = sqlite3_open([[self paths] UTF8String], &database);
//    const char *sql = "update info set inf = 99 where id = 2;";
//    char *errorMsg;
//    int success = sqlite3_exec(database, sql, NULL,NULL,&errorMsg);
//    sqlite3_close(database);
//    if(success == SQLITE_OK)
//        return 22;
//    else 
//        return 33;
//}

- (BOOL)updateData:(NSArray *)aryData toTable:(NSString *)tableName
{
    NSDictionary *dic = aryData[0];
    NSString *strValueKey = [dic objectForKey:DataBaseValueKey];
    NSString *strValue = [dic objectForKey:DataBaseValue];
    
    NSString *strSql = [NSString stringWithFormat:@"UPDATE %@ SET Value = '%@' where ValueKey = '%@';", tableName, strValue, strValueKey];
    char *errMsg = NULL;
    if (sqlite3_exec(db, [strSql UTF8String], NULL, NULL, &errMsg) != SQLITE_OK)
    {
        sqlite3_free(errMsg);
        return NO;
    }
    return YES;
}

- (id)queryDataWithKey:(NSString *)key inTable:(NSString *)tableName
{
    NSString *strValue = nil;
    NSString *strSql = [NSString stringWithFormat:@"SELECT Value FROM %@ WHERE ValueKey = '%@';", tableName, key];
    sqlite3_stmt *stmt = NULL;
    if (sqlite3_prepare_v2(db, [strSql UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            const char *chValue = (const char *)sqlite3_column_text(stmt, 0);
            strValue = [NSString stringWithCString:chValue encoding:NSUTF8StringEncoding];
        }
    }
    sqlite3_finalize(stmt);
    
    return strValue;
}

@end
